<?php 
class Mconsignment extends CI_Model
{
	function Mconsignment()
	{
		parent::__construct();
	}

  public function termins() {
    $query =$this->db->get_where("t_mtr_consignment_term",array("istatus" => 1));
    return $query->result();
  }

  public function validate_item($iccid, $sales_id, $territory_id)
  {
    $this->db->select('iccid');
    $this->db->where("iccid", $iccid );
    $this->db->where("istatus", 1 );
    $validate_iccid_for_sellin = $this->db->get("t_trx_consignment_detail");
    
    if ( $query->num_rows() == 0 ) 
        { return null; }
    else
        { return $iccid; }

    $x = array();
    foreach ($iccid as $key => $val) 
      { $x[] = $val['iccid']; }
    return $x;
  }

  public function save_consignment($sales_id, $territory_id, $top_id, $trx_date, $remark, $cluster_id, $region_id)
  {
    $data = array(
        'territory_id'     => $territory_id,
        'user_id'          => $sales_id,
        'term_id'          => $top_id,
        'istatus'          => 1,
        'consignment_date' => $trx_date,
        'remark'           => $remark,
        'created_on'       => date('Y-m-d H:i:s'),
        'created_by'       => $this->session->userdata('username'),
        'acess_via'        => 'web',
        'cluster_id'       => $cluster_id,
        'region_id'        => $region_id
      );
    $this->db->insert('t_trx_consignment', $data);
    return $this->db->insert_id();
  }

  public function consignment_program($trx_id, $program_id)
  {
    $data = array(
        'consignment_id' => $trx_id,
        'program_id'     => $program_id,
        'created_by'     => $this->session->userdata('username')
      );
    $this->db->insert('t_trx_consignment_program', $data);
  }

  public function save_consignment_detail($trx_id, $iccid)
  {
    $data = array(
      'consignment_id' => $trx_id,
      'iccid'          => $iccid,
      'istatus'        => 1,
      'created_by'     => $this->session->userdata('username')
    );
    $this->db->insert('t_trx_consignment_detail', $data);
    $data2 = array(
        'istatus' => 5,
        'transaction_type' => 'Konsinyasi',
        'transaction_id' => $trx_id
    );
    $this->db->update('t_trx_scan_in_detail', $data2, array('iccid' => $iccid));
  }

	public function detail($id)
	{
		$user_login = $this->session->userdata("username");
		$this->db->select("t_trx_consignment.consignment_id, t_trx_consignment.user_id, t_mtr_user.user_name");
		$this->db->select("t_mtr_user_group.user_group_caption, t_trx_consignment.territory_id");
		$this->db->select("f_get_territory_path( f_get_cluster('$user_login') ) as territory, t_mtr_territory.territory_name");
		$this->db->select("f_get_channel_id_by_territory_id(t_trx_consignment.territory_id) as channel");
		$this->db->select("t_trx_consignment.istatus, t_mtr_consignment_term.term_day");
		$this->db->select("t_trx_consignment.consignment_date, t_trx_consignment.remark");

		$this->db->join("t_mtr_territory","t_mtr_territory.territory_id = t_trx_consignment.territory_id");
		$this->db->join("t_mtr_consignment_term","t_mtr_consignment_term.term_id = t_trx_consignment.term_id");
		$this->db->join("t_mtr_user","t_mtr_user.user_id = t_trx_consignment.user_id");
		$this->db->join("t_mtr_user_group","t_mtr_user_group.user_group_id = t_mtr_user.user_group_id");

		$query = $this->db->get_where("t_trx_consignment",array("consignment_id" => $id));

		return $query->row();
	}

	public function items($id)
	{

    $consignment_id = 'consignment_'.$id;
    $query_string = " SELECT dd.detail_id, dd.iccid, dd.istatus, mm.item_name, gg.item_group_name, f_get_price (dd.iccid, hh.user_id) AS price, sris.status,
                      hh.consignment_date, sris.activation_date
                      FROM t_trx_consignment_detail dd
                      JOIN t_trx_consignment hh ON hh.consignment_id = dd.consignment_id
                      JOIN t_mtr_item ii ON ii.iccid = dd.iccid
                      JOIN t_mtr_item_group gg ON gg.item_group_id = ii.item_group_id
                      JOIN t_mtr_item_mapping mm ON mm.item_code = ii.item_code
                      JOIN ( SELECT p.* FROM dblink( key(), 
                             'SELECT serial_iccid, created_by, status, activation_date from t_mtr_item 
                              WHERE created_by = ''$consignment_id'' ' ) 
                              AS p(serial_iccid VARCHAR, created_by VARCHAR, status INT, activation_date DATE) 
                           ) AS sris ON  sris.serial_iccid = dd.iccid
                      WHERE dd.consignment_id = $id ";

		$query = $this->db->query($query_string, false);
		return $query->result();
	}

	public function takeout($consignment_id) {
		$this->db->update("t_trx_consignment",array("istatus" => 2), array("consignment_id" => $consignment_id) );
	}

  public function validate_iccid_for_sellin($iccid)
  {
    $this->db->select('iccid');
    $this->db->where_in('iccid', $iccid);
    $query = $this->db->get('t_trx_sell_in_detail');
    $iccid = $query->result_array();
    $x = array();
    foreach ($iccid as $key => $val) 
      { $x[] = $val['iccid']; }
    return $x;
  }

  public function createSellIn($consignment_id, $device = 'web') {
    // Ambil informasi yang dibutuhkan untuk membuat sell in, (channel_id dan user_id)
    $this->db->select("user_id, f_get_channel_id_by_territory_id(territory_id) as channel_id");
    $this->db->where("consignment_id", $consignment_id);
    $query =  $this->db->get("t_trx_consignment");
    $consignment = $query->row();

    // Berikut ini data yang akan dimasukin ke tabel sell_in
    $sell_in = array(
      "channel_id" => $consignment->channel_id,
      "user_id" => $consignment->user_id,
      "acess_via" => $device,
      "top_id" => 1,
      "created_by" => $this->session->userdata("username"),
      "updated_by" => $this->session->userdata("username"),
      "sell_in_date" => date('Y-m-d')
    );
    $this->db->insert("t_trx_sell_in", $sell_in);
    $newSellIn = $this->db->insert_id();

    //patch by donny, insert program konsinyasi ke program sellin
    $this->db->select("program_id");
    $this->db->where("consignment_id", $consignment_id);
    $query = $this->db->get("t_trx_consignment_program");
    $consignment_program = $query->result();

    foreach ($consignment_program as $cp){
      $sell_in_program = array(
        "sell_in_id" => $newSellIn,
        "program_id" => $cp->program_id,
        "created_by" => $this->session->userdata("username"),
        "updated_by" => $this->session->userdata("username"),
        "created_on" => date("Y-m-d H:i:s"),
        "updated_on" => date("Y-m-d H:i:s")
      );
      $this->db->insert("t_trx_sell_in_program", $sell_in_program);
      $newSellInprogram = $this->db->insert_id();
    }

    // Sebagai penghubung antara tabel consignment dan sell in, transaksi ini kita simpan ke "Consignment_sell_in"
    $data = array(
      "consignment_id" => $consignment_id,
      "sell_in_id"     => $newSellIn
    );
    $this->db->insert("t_trx_consignment_sell_in", $data);

    return $newSellIn;
  }


    function sellInDetail($sell_in_id, $iccid, $consignment_id) {
      foreach ($iccid as $key) {
        $data[] = array(
                    "iccid"      => $key,
                    "sell_in_id" => $sell_in_id,
                    "istatus"    => 1,
                    "created_on" => date('Y-m-d H:i:s'),
                    "created_by" => $this->session->userdata('username'),
                  );
      }

        $this->db->insert_batch("t_trx_sell_in_detail", $data);

        $this->db->where('consignment_id', $consignment_id);
        $this->db->where_in('iccid', $iccid);
        $this->db->update("t_trx_consignment_detail", array("istatus" => 3));

        //update istatus pada table t_trx_scan_in_detail menjadi 2
        $this->db->where('transaction_id', $consignment_id);
        $this->db->where_in('iccid', $iccid);
        $this->db->update('t_trx_scan_in_detail', array('istatus' => 2, 'transaction_type' => 'Konsinyasi - Terjual', 'transaction_id' => $sell_in_id));
    }

	function normalize($trx_id, $iccid) {

		$data = array(
				'istatus' => 1,
				'transaction_type' => 'Konsinyasi - Tidak terjual'
			); 

    $this->db->where('transaction_type', 'Konsinyasi');
    $this->db->where('transaction_id', $trx_id);
    $this->db->where_in('iccid', $iccid);
		$this->db->update('t_trx_scan_in_detail', $data);

    $this->db->where('consignment_id', $trx_id);
    $this->db->where_in('iccid', $iccid);
		$this->db->update("t_trx_consignment_detail", array("istatus" => 2) );

	}
}

?>